﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ExcelLibrary.SpreadSheet;
using System.IO;
using NPOI.HSSF.UserModel;

namespace RepairChecker
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                caleStart.SelectedDate = DateTime.Now;
                caleEnd.SelectedDate = DateTime.Now;
            }


            //Workbook workbook = Workbook.Load("d:/text.xls");
            //Worksheet worksheet = workbook.Worksheets[0];

            
            //for (int i = 0; i < worksheet.Cells.Rows.Count; i++)
            //{
            //    Response.Write(worksheet.Cells[i, 1].Value + "<br/>");
                
                    
            //}
            //Response.End();
        }

        protected void Unnamed1_Click(object sender, EventArgs e)
        {
            //MemoryStream ms;
            //Entity.OutputWeeklyReport(9, 2011,out ms);
            //ExportToExcel(ms);



            List<Job> jobs=Entity.GetJobs(0,0,3,0,"",4,caleStart.SelectedDate,caleEnd.SelectedDate);
            const int OFFSETROW = 2, OFFSETCOL = 0;

            FileStream fs = new FileStream(@"MonthlyTemplate.xls",FileMode.Open,FileAccess.Read);

            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            var sheet = workbook.GetSheetAt(0);
            sheet.GetRow(0).GetCell(0).SetCellValue (caleStart.SelectedDate.ToShortDateString() + " - " + caleEnd.SelectedDate.ToShortDateString() + " 月统计表");
            foreach (Job j in jobs)
            {
                if (j.FaultIdString.Contains("1,"))
                {
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 10).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 10).NumericCellValue + 1);
                }

                if (j.FaultIdString.Contains("2,"))
                {
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 10).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 10).NumericCellValue + 1);
                }

                if (j.FaultIdString.Contains("3,"))
                {
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 12).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 12).NumericCellValue + 1);
                }

                if (j.FaultIdString.Contains("4,"))
                {
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 9).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 9).NumericCellValue + 1);
                }

                if (j.FaultIdString.Contains("5,"))
                {
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 7).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 7).NumericCellValue + 1);
                }

                if (j.FaultIdString.Contains("6,"))
                {
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 1).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 1).NumericCellValue + 1);
                }

                if (j.FaultIdString.Contains("7,"))
                {
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 13).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 13).NumericCellValue + 1);
                }

                if (j.FaultIdString.Contains("8,"))
                {
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 13).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 13).NumericCellValue + 1); 
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 19).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 19).NumericCellValue + 1);
                }

                if (j.FaultIdString.Contains("10,"))
                {
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 6).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 6).NumericCellValue + 1);
                }

                if (j.FaultIdString.Contains("11,"))
                {
                    sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 8).SetCellValue(sheet.GetRow(OFFSETROW + j.DistrictId).GetCell(OFFSETCOL + 8).NumericCellValue + 1);
                }



            }
            sheet.ForceFormulaRecalculation = true;

            //FileStream fs2 = new FileStream(@"d:\aa.xls", FileMode.Create);
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ExportToExcel(ms);
            ms.Close();
          

        }
        protected void ExportToExcel(MemoryStream ms)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment;filename=MyFiles.xls");
            Response.Charset = "";
            this.EnableViewState = false;

            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);

            //GridView1.RenderControl(htw);
            //FileStream fs = new FileStream("out.xls", FileMode.Open);

            Response.OutputStream.Write(ms.ToArray(), 0, ms.ToArray().Length);
            
            //Response.Write(ms.ToArray(), 0, (long)ms.Length); ;
            Response.End();
        }

        protected void btnWeekly_Click(object sender, EventArgs e)
        {
            List<Job> jobs = Entity.GetJobs(0, 0, 3, 0, "", 4, caleStart.SelectedDate, caleEnd.SelectedDate);

            FileStream fs = new FileStream(@"WeeklyTemplate.xls", FileMode.Open, FileAccess.Read);

            HSSFWorkbook workbook = new HSSFWorkbook(fs);

            foreach (Job j in jobs)
            {
                var sheet = workbook.GetSheet(j.CrewName);
                int lastRow = sheet.LastRowNum;
                if (lastRow==0)
                {
                    sheet.CreateRow(0).CreateCell(0);

                    var font = workbook.CreateFont();
                    var style = workbook.CreateCellStyle();
                    style.SetFont(font);
                    sheet.CreateRow(0).CreateCell(0).CellStyle = style;
                    font.FontHeightInPoints = 15;
                    font.Boldweight = 5;
                    sheet.GetRow(0).GetCell(0).SetCellValue(j.CrewName + " " + caleStart.SelectedDate.ToShortDateString() + " - " + caleEnd.SelectedDate.ToShortDateString() + " 周统计表");
                    

                }
                lastRow++;
                var row = sheet.CreateRow(lastRow);
                row.CreateCell(0).SetCellValue(j.FinishDate.Split(' ').GetValue(0).ToString());
                row.CreateCell(1).SetCellValue(j.CrewName);
                row.CreateCell(2).SetCellValue(j.DistrictName + j.OfficeName);
                row.CreateCell(3).SetCellValue(j.JobType);
                row.CreateCell(4).SetCellValue(j.ArriveDate.Split(' ').GetValue(1).ToString());
                row.CreateCell(5).SetCellValue(j.FinishDate.Split(' ').GetValue(1).ToString());

                string faultString = string.Empty;
                if (!String.IsNullOrEmpty(j.FaultIdString))
                {

                    List<Fault> faults = Entity.GetAllFaults(true);

                    foreach (string s in j.FaultIdString.Split(','))
                    {
                        if (!string.IsNullOrEmpty(s)) //
                        {
                            foreach (Fault f in faults)
                            {
                                if (f.FaultId == int.Parse(s))
                                {
                                    faultString += f.FaultName;
                                    break;
                                }
                            }
                        }

                    }




                }
                row.CreateCell(5).SetCellValue(faultString);
            }

            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ExportToExcel(ms);
            ms.Close();

        }

    }
}